/*
 * CSVToXMLConverter.java
 *
 * Created on December 20, 2007, 5:26 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
package app_99;

import java.sql.*;
import java.io.*;
import java.text.SimpleDateFormat;

/**
 * 
 * @author artani
 */
public class CSVtoDB {

    private static final String CSV_FILE_PATH = "C:/Documents and Settings/artani/Desktop/APP_DEV/Res/data/";
    private static final String CSV_FILE = "MVPData";
    private static final String CSV_DRIVER = "org.relique.jdbc.csv.CsvDriver";
    private Connection dbConn = null;

    /** Creates a new instance of CSVToXMLConverter 
     * This is an empty constructor, auto generated by NetBeans 5.5
     * Due to the trivial nature of the program, all the class variables have been
     * initialized at the top as constants; therefore, since the driver or any 
     * other class or module pass any information to this class, the constructor is
     * only a placeholer. This empty constructor could be used in the future if the
     * class interfaces with other modules or systems.  
     */
    public CSVtoDB() {
    //empty constructor
    }

    private void connectToDB() {
        try {
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver).newInstance();
            dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/MYSQL_TEST", "root", "");
            dbConn.setAutoCommit(false);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (IllegalAccessException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (InstantiationException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * This method first opens a connection string to the csv file and reads it like a database table,
     * using SQL. The result set is stored into the results variable and from there each line is 
     * inspected and the data is extracted to be converted to XML. 
     * This method uses only one custom method in this class, whose purpose is to convert a
     * string from any format to captialized. 
     */
    public void createDates() throws Exception {
        try {
            connectToDB();
            Class.forName(CSV_DRIVER);
            Connection conn = DriverManager.getConnection("jdbc:relique:csv:" + CSV_FILE_PATH);
            Statement stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select Rep_date from " + CSV_FILE);

            while (results.next()) {
                //insert to database here
                PreparedStatement pstmt = dbConn.prepareStatement("UPDATE mvpdata SET RepDate = ? where Rep_date = ? ");
                java.util.Date date = makeDate(results.getString(1));
                pstmt.setDate(1, (Date)date);
                pstmt.setString(2, results.getString(1));
                pstmt.execute();
                pstmt.close();
            }

        } catch (SQLException ex) {
            System.out.println("Error while reading CSV file.");
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    public void convert() throws Exception {
        try {
            connectToDB();
            Class.forName(CSV_DRIVER);
            Connection conn = DriverManager.getConnection("jdbc:relique:csv:" + CSV_FILE_PATH);
            Statement stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from " + CSV_FILE);

            // Iterate through the result set of the stmt query and create the document tree to output to xml
            while (results.next()) {
                //insert to database here
                PreparedStatement pstmt = dbConn.prepareStatement("INSERT INTO mvpdata (RepDate, Establishment, TotalVisitors, PeekVisitors, AvgVisitors, Income, AvgPerPatron) " +
                        "VALUES(?, ?, ?, ?, ?, ?, ?)");
                java.util.Date date = makeDate(results.getString(1));
                java.sql.Date mySqlDate = new java.sql.Date(date.getTime());
                pstmt.setDate(1, mySqlDate);
                pstmt.setString(2, results.getString(2));
                pstmt.setFloat(3, results.getFloat(3));
                pstmt.setFloat(4, results.getFloat(4));
                pstmt.setFloat(5, results.getFloat(5));
                pstmt.setFloat(6, results.getFloat(6));
                pstmt.setFloat(7, results.getFloat(7));
                pstmt.execute();
                pstmt.close();
            }

            dbConn.commit();
            stmt.close(); //release this Statement object's database and JDBC resources
            conn.close(); //release this Connection object's database and JDBC resources
            dbConn.close();
        } catch (SQLException ex) {
            System.out.println("Error while reading CSV file.");
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Takes the incoming String parameter and returns the capitalized form of it.
     *
     * @param winner 
     * @return winner capitalized, such as Winner
     */
    public String capitalize(String winner) {
        return winner.substring(0, 1).toUpperCase() + winner.substring(1).toLowerCase();
    }

    public static java.util.Date makeDate(String dateString) throws Exception {
        SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
        return formatter.parse(dateString);
    }
}
